import pymysql
import csv
import re
import os
import sys
import time
import datetime

# 创建日志文件
log_file = f"product_import_{datetime.datetime.now().strftime('%Y%m%d_%H%M%S')}.log"

def log(message):
    """将消息写入日志文件并打印到控制台"""
    timestamp = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    log_message = f"[{timestamp}] {message}"
    
    # 写入日志文件
    with open(log_file, "a", encoding="utf-8") as f:
        f.write(log_message + "\n")
    
    # 打印到控制台
    print(message)

# 记录开始信息
log("===== 产品数据导入工具 =====")
log(f"Python版本: {sys.version}")
log(f"当前工作目录: {os.getcwd()}")
log(f"日志文件: {os.path.abspath(log_file)}")

# 定义CSV文件路径
csv_path = 'backend/data/products.csv'  # 相对于当前工作目录
log(f"CSV文件路径: {csv_path}")
if not os.path.exists(csv_path):
    log(f"错误：找不到CSV文件 {csv_path}")
    sys.exit(1)
else:
    log(f"CSV文件存在，大小：{os.path.getsize(csv_path)/1024:.2f} KB")

# 尝试打开并读取CSV文件的前几行
try:
    with open(csv_path, 'r', encoding='utf-8') as f:
        first_lines = [next(f) for _ in range(3)]
        log("CSV文件前几行内容:")
        for i, line in enumerate(first_lines):
            log(f"  行 {i+1}: {line.strip()}")
except UnicodeDecodeError:
    log("UTF-8编码读取失败，尝试GB2312/GBK编码")
    try:
        with open(csv_path, 'r', encoding='gbk') as f:
            first_lines = [next(f) for _ in range(3)]
            log("CSV文件前几行内容 (GBK编码):")
            for i, line in enumerate(first_lines):
                log(f"  行 {i+1}: {line.strip()}")
    except:
        log("GBK编码也失败，将在导入过程中尝试多种编码")

# 数据库连接
log("\n===== 连接数据库 =====")
try:
    connection = pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        database='premade_food_trace',
        charset='utf8mb4'
    )
    log("数据库连接成功")
except Exception as e:
    log(f"数据库连接失败: {e}")
    sys.exit(1)

# 定义正则表达式检查 code 格式
code_pattern = re.compile(r'^SP-\d{8}-\d{3}$')

try:
    with connection.cursor() as cursor:
        # 首先获取所有有效的supplier_id
        log("\n===== 检查有效供应商 =====")
        cursor.execute("SELECT id FROM suppliers")
        supplier_records = cursor.fetchall()
        valid_supplier_ids = {row[0] for row in supplier_records}
        log(f"数据库中发现 {len(valid_supplier_ids)} 个有效供应商ID")
        
        if not valid_supplier_ids:
            log("警告: 数据库中没有找到任何供应商ID，所有产品都将被跳过")
            sys.exit(1)

        # 导入统计数据
        total_records = 0
        imported_records = 0
        skipped_invalid_code = 0
        skipped_invalid_supplier = 0
        
        log("\n===== 开始导入产品数据 =====")
        start_time = time.time()
        
        # 尝试不同的编码打开CSV文件
        encodings = ['utf-8', 'gbk', 'gb18030', 'latin1']
        success = False
        
        for encoding in encodings:
            if success:
                break
                
            try:
                log(f"\n尝试使用 {encoding} 编码读取CSV文件...")
                
                with open(csv_path, encoding=encoding) as csvfile:
                    reader = csv.DictReader(csvfile)
                    
                    # 检查列名
                    fieldnames = reader.fieldnames
                    if not fieldnames:
                        log(f"警告：无法读取CSV列名，尝试下一种编码")
                        continue
                        
                    log(f"CSV列名: {', '.join(fieldnames)}")
                    
                    if 'supplier_id' not in fieldnames:
                        log("错误: CSV文件中缺少supplier_id列")
                        break
                    
                    # 开始导入数据
                    log(f"\n使用{encoding}编码开始读取数据...")
                    
                    for row in reader:
                        total_records += 1
                        
                        # 每100条记录显示一次进度
                        if total_records % 100 == 0:
                            elapsed = time.time() - start_time
                            log(f"已处理 {total_records} 条记录... (用时: {elapsed:.2f}秒)")
                        
                        # 验证 code 格式
                        if 'code' not in row or not row['code'] or not code_pattern.match(row['code']):
                            code_value = row.get('code', '缺失')
                            if total_records <= 5:
                                log(f"跳过无效的产品编码: {code_value}")
                            skipped_invalid_code += 1
                            continue

                        # 转换supplier_id为整数并验证存在性
                        try:
                            supplier_id = int(row['supplier_id'])
                            if supplier_id not in valid_supplier_ids:
                                if total_records <= 5:
                                    log(f"跳过不存在的供应商ID: {supplier_id}")
                                skipped_invalid_supplier += 1
                                continue  # 跳过不存在的供应商ID
                        except (ValueError, KeyError) as e:
                            if total_records <= 5:
                                log(f"供应商ID格式无效: {row.get('supplier_id', '缺失')}")
                            skipped_invalid_supplier += 1
                            continue

                        # 确保所有必要字段都有值
                        for field in ['name', 'category', 'description', 'shelf_life', 
                                      'storage_conditions', 'net_weight', 'nutrition_info', 
                                      'image_url', 'status']:
                            if field not in row or not row[field]:
                                if field == 'status':
                                    row[field] = 'active'  # 默认状态
                                else:
                                    row[field] = ''  # 其他字段默认为空字符串

                        # 插入数据
                        try:
                            sql = """
                            INSERT INTO products (
                                name, supplier_id, code, category, description, shelf_life, 
                                storage_conditions, net_weight, nutrition_info, image_url, status
                            ) VALUES (
                                %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
                            )
                            """
                            cursor.execute(sql, (
                                row['name'], supplier_id, row['code'], row['category'], row['description'],
                                row['shelf_life'], row['storage_conditions'], 
                                row['net_weight'], row['nutrition_info'], row['image_url'], 
                                row['status']
                            ))
                            imported_records += 1
                            
                            # 每50条记录提交一次
                            if imported_records % 50 == 0:
                                connection.commit()
                                elapsed = time.time() - start_time
                                log(f"已导入 {imported_records} 条记录... (用时: {elapsed:.2f}秒)")
                            
                        except pymysql.err.IntegrityError as e:
                            if "Duplicate entry" in str(e):
                                if total_records <= 5:
                                    log(f"跳过重复产品编码: {row['code']}")
                            else:
                                raise
                    
                    # 最终提交
                    connection.commit()
                    success = True
                    elapsed = time.time() - start_time
                    log(f"\n成功使用 {encoding} 编码导入数据，总用时: {elapsed:.2f}秒")
                    
            except UnicodeDecodeError:
                log(f"{encoding} 编码不适用，尝试下一种编码...")
            except Exception as e:
                log(f"使用 {encoding} 编码时出错: {e}")
                import traceback
                log(traceback.format_exc())
                if encoding == encodings[-1]:  # 如果是最后一种编码尝试
                    raise
        
        # 打印导入结果摘要
        log(f"\n===== 导入结果摘要 =====")
        log(f"总记录数: {total_records}")
        log(f"成功导入: {imported_records}")
        log(f"跳过无效编码: {skipped_invalid_code}")
        log(f"跳过无效供应商: {skipped_invalid_supplier}")
        
        # 验证数据库中的产品数量
        cursor.execute("SELECT COUNT(*) FROM products")
        product_count = cursor.fetchone()[0]
        log(f"数据库中的产品总数: {product_count}")

except Exception as e:
    log(f"\n导入过程中出错: {e}")
    import traceback
    log(traceback.format_exc())
    connection.rollback()  # 发生错误时回滚事务
finally:
    connection.close()
    log("\n数据库连接已关闭")
    total_time = time.time() - start_time if 'start_time' in locals() else 0
    log(f"===== 导入过程结束，总用时: {total_time:.2f}秒 =====")
    log(f"日志已保存到: {os.path.abspath(log_file)}")

# 强制刷新输出
sys.stdout.flush()